package edu.zju.gis.dbfg.server.util;

import edu.zju.gis.dbfg.server.model.ExternalDataArgs;
import edu.zju.gis.dbfg.server.model.vo.FeatureRecord;
import edu.zju.gis.dbfg.server.model.vo.tableRecordsWithTotalsize;
import org.json.JSONArray;
import org.json.simple.JSONObject;

import java.sql.DriverManager;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class MysqlHelper extends ExternalHelper {
    public  JSONObject testConnection(ExternalDataArgs sourceConnect) throws Exception{
        Class.forName("com.mysql.cj.jdbc.Driver");
        String url = "jdbc:mysql://" + sourceConnect.getIp() + ":" + sourceConnect.getPort() + "/" + sourceConnect.getDatabaseName()+"?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=GMT%2B8";
        this.connection = DriverManager.getConnection(url,
                sourceConnect.getUserName(), sourceConnect.getPassword());
        System.out.println("Opened database successfully");
        return  this.res;
    }

    public List<JSONObject> queryConstruct(ExternalDataArgs sourceConnect)throws Exception{
        List<JSONObject> resObject = new ArrayList<>();
        if (this.connection==null || this.connection.isClosed()){
            testConnection(sourceConnect);
        }
        Statement stmt = this.connection.createStatement();
        if (sourceConnect.getTableName() == null) {
            //获取表名列表
//            String selectSql = "show tables from "+sourceConnect.getDatabaseName();
            String selectSql = "select table_name from information_schema.tables where table_schema='"+sourceConnect.getDatabaseName()+"'";
            resObject = querySql(stmt, selectSql, "table_name");
        } else if (sourceConnect.getFields() == null) {
            String sqlfields="select COLUMN_NAME from information_schema.COLUMNS where table_name ='"+sourceConnect.getTableName()+"' and table_schema = '"+sourceConnect.getDatabaseName()+"'";
            resObject = querySql(stmt, sqlfields, "column_name");
        }
        return resObject;
    }

    public tableRecordsWithTotalsize pageSearh(ExternalDataArgs sourceConnect, int pageNo, int pageSize) throws Exception{
        tableRecordsWithTotalsize tableRecordsWithTotalsize = new tableRecordsWithTotalsize();
        if (this.connection==null || this.connection.isClosed()) {
            testConnection(sourceConnect);
        }
        Statement stmt = this.connection.createStatement();
        //获取字段
        List<JSONObject> jsonObjects = queryConstruct(sourceConnect);
        String colName = jsonObjects.get(0).get("value").toString();
        //从第3条开始，取10条
//                select * from testwmx order by name desc limit 10 offset 3
        int offset=(pageNo-1)*pageSize;
        String sql="";
        //有空间字段,返回空间字段的geojson格式
        if(!sourceConnect.getSpatialField().equals("none")){
            sql = "select *,ST_AsGeoJSON("+ sourceConnect.getSpatialField()+") from " + sourceConnect.getTableName()+" order by "+colName+" desc limit "+pageSize+" offset "+ offset;
        }else{
            sql = "select * from " + sourceConnect.getTableName()+" order by "+colName+" desc limit "+pageSize+" offset "+ offset;
        }
        String sqlAll="select * from " + sourceConnect.getTableName();
        tableRecordsWithTotalsize = search(stmt, sql,sqlAll,sourceConnect.getSpatialField());
        return tableRecordsWithTotalsize;
    }

    public FeatureRecord identifyQuery(String lon, String lat, ExternalDataArgs sourceConnect) throws Exception {
        FeatureRecord featureRecord = new FeatureRecord();
        //根据sourceConnect得出坐标系
        //然后进行查询
        return featureRecord;
    }

    public List<FeatureRecord> areaQuery(JSONArray areaArray, ExternalDataArgs sourceConnect) throws Exception {
        //参数为前后闭合的polygon
        List<FeatureRecord> featureRecord = new ArrayList<>();
        return featureRecord;
    }
}
